﻿Imports SIGI.Common.BE
Imports System.Data.SqlClient
Imports System.Configuration

Public Class ServicesDAC

#Region "<< -Search- >>"
    Public Shared Function Search(pDeleted As Boolean) As List(Of ServicesBE)
        Return SearchByParam(New ServicesBE With {.Deleted = pDeleted})
    End Function
#End Region

#Region "<< -SearchByParam- >>"
    Public Shared Function SearchByParam(pService As ServicesBE) As List(Of ServicesBE)
        Dim wSqlConn As SqlConnection = Nothing
        Dim wSqlCmd As SqlCommand = Nothing
        Dim wReader As SqlDataReader = Nothing
        Dim wConnStr As String = String.Empty

        Dim wServices As List(Of ServicesBE) = Nothing
        Dim wService As ServicesBE = Nothing

        Try
            'Obtengo la cadena de conexión
            wConnStr = ConfigurationManager.ConnectionStrings("connString").ConnectionString
            'Creo la conexión a la BD
            wSqlConn = New SqlConnection(wConnStr)
            'Creo el comando
            wSqlCmd = New SqlCommand()
            wSqlCmd.Connection = wSqlConn
            wSqlCmd.CommandType = CommandType.StoredProcedure
            wSqlCmd.CommandText = "Servicios_g"

            wSqlCmd.Parameters.Add("@Id_Servicio", SqlDbType.Int)
            wSqlCmd.Parameters.Add("@Descripcion", SqlDbType.Char, 100)
            wSqlCmd.Parameters.Add("@Borrado", SqlDbType.Bit)

            If pService.Id.HasValue Then
                wSqlCmd.Parameters("@Id_Servicio").Value = pService.Id.Value
            End If
            If Not String.IsNullOrEmpty(pService.Description) Then
                wSqlCmd.Parameters("@Descripcion").Value = pService.Description
            End If
            If pService.Deleted.HasValue Then
                wSqlCmd.Parameters("@Borrado").Value = Convert.ToByte(pService.Deleted)
            End If

            wSqlConn.Open()

            wReader = wSqlCmd.ExecuteReader()
            wServices = New List(Of ServicesBE)
            While wReader.Read()
                wService = New ServicesBE
                With wService
                    .Id = Convert.ToInt32(wReader("Id_Servicio"))
                    .Description = Convert.ToString(wReader("Descripcion"))
                    .Deleted = Convert.ToBoolean(wReader("Borrado"))
                End With

                wServices.Add(wService)
            End While

            wSqlConn.Close()
        Catch ex As Exception
            Throw ex
        End Try

        Return wServices
    End Function

#End Region

#Region "<< -SearchByPropertyId- >>"
    Public Shared Function SearchByPropertyId(pPropertyId As Integer) As List(Of ServicesBE)
        Dim wSqlConn As SqlConnection = Nothing
        Dim wSqlCmd As SqlCommand = Nothing
        Dim wReader As SqlDataReader = Nothing
        Dim wConnStr As String = String.Empty

        Dim wServices As List(Of ServicesBE) = Nothing
        Dim wService As ServicesBE = Nothing

        Try
            'Obtengo la cadena de conexión
            wConnStr = ConfigurationManager.ConnectionStrings("connString").ConnectionString
            'Creo la conexión a la BD
            wSqlConn = New SqlConnection(wConnStr)
            'Creo el comando
            wSqlCmd = New SqlCommand()
            wSqlCmd.Connection = wSqlConn
            wSqlCmd.CommandType = CommandType.StoredProcedure
            wSqlCmd.CommandText = "ServiciosXPropiedades_g"

            wSqlCmd.Parameters.Add("@Id_Propiedad", SqlDbType.Int)
            wSqlCmd.Parameters("@Id_Propiedad").Value = pPropertyId

            wSqlConn.Open()

            wReader = wSqlCmd.ExecuteReader()
            wServices = New List(Of ServicesBE)
            While wReader.Read()
                wService = New ServicesBE
                With wService
                    .Id = Convert.ToInt32(wReader("Id_Servicio"))
                    .Description = pPropertyId
                End With

                wServices.Add(wService)
            End While

            wSqlConn.Close()
        Catch ex As Exception
            Throw ex
        End Try

        Return wServices
    End Function
#End Region

#Region "<< -Insert Batch- >>"
    Public Shared Sub InsertBacth(pServices As List(Of ServicesBE), Optional pCmd As SqlCommand = Nothing)
        Dim wSqlConn As SqlConnection = Nothing
        Dim wSqlCmd As SqlCommand = Nothing
        Dim wConnStr As String = String.Empty
        Dim wQuery As System.Text.StringBuilder = Nothing

        Try
            If pServices Is Nothing OrElse pServices.Count = 0 Then Return

            If Not pCmd Is Nothing Then
                wSqlConn = pCmd.Connection
                wSqlCmd = pCmd
                wSqlCmd.Parameters.Clear()
            Else
                'Obtengo la cadena de conexión
                wConnStr = ConfigurationManager.ConnectionStrings("connString").ConnectionString
                'Creo la conexión a la BD
                wSqlConn = New SqlConnection(wConnStr)
            End If

            wQuery = New System.Text.StringBuilder
            For Each wSrv As ServicesBE In pServices
                wQuery.Append("EXEC ServiciosXPropiedades_i ")
                wQuery.Append(String.Format(" @Id_Propiedad = {0} ", wSrv.IdProperty))
                wQuery.Append(String.Format(" @Id_Servicio = {0} ", wSrv.Id))
                wQuery.Append(vbNewLine)
            Next
            wSqlCmd.CommandType = CommandType.Text
            wSqlCmd.CommandText = wQuery.ToString()

            If wSqlConn.State <> ConnectionState.Open Then wSqlConn.Open()

            wSqlCmd.ExecuteNonQuery()
            If pCmd Is Nothing Then wSqlConn.Close()
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

#End Region

End Class
